﻿CREATE PROCEDURE [dbo].[spMonitor_Alerts]
AS

SET NOCOUNT ON;

--- Declare Local Variables
DECLARE @t TABLE (
	[name] [sysname],
	[severity] [int],
	[message_id] [int],
	[enabled] [tinyint],
	[occurrence_count] [int],
	[log_date] [smalldatetime]
)

--- Get a list of alerts with notifications sent
INSERT INTO @t
	SELECT		[name],
				[severity],
				[message_id],
				[enabled],
				[occurrence_count],
				CAST(GETDATE() as smalldatetime) [log_date]
	FROM		[msdb].[dbo].[sysalerts]
	WHERE		[occurrence_count] > 0
				AND (
					[severity] BETWEEN 17 AND 25
					OR [message_id] = 825
				)
	ORDER BY	[severity],
				[message_id]

--- Reset Counter
DECLARE @name sysname, @curr_date int, @curr_time int
DECLARE crs CURSOR FOR
	SELECT		[name],
				[curr_date] = CONVERT(INT, CONVERT(CHAR, GETDATE(), 112)),
				[curr_time] = (DATEPART(hh, GETDATE()) * 10000) + (DATEPART(mi, GETDATE()) * 100) + (DATEPART(ss, GETDATE()))
	FROM		@t
OPEN crs
FETCH NEXT FROM crs INTO @name, @curr_date, @curr_time
WHILE @@FETCH_STATUS <> -1
  BEGIN
	EXEC	msdb.dbo.sp_update_alert	
				@name = @name,
				@count_reset_date = @curr_date,
				@count_reset_time = @curr_time,
				@occurrence_count = 0

	FETCH NEXT FROM crs INTO @name, @curr_date, @curr_time
  END
CLOSE crs
DEALLOCATE crs

--- Log the current alert counts/list to the table
INSERT INTO [dbo].[tblMonitor_Alerts]
(
	[alt_name],
	[alt_severity],
	[alt_message_id],
	[alt_enabled],
	[alt_occurrence_count],
	[alt_log_date]
)
SELECT		[name],
			[severity],
			[message_id],
			[enabled],
			[occurrence_count],
			[log_date]
FROM		@t

SET NOCOUNT OFF;
GO